﻿using DHQGHN.DAL.Base;
using DHQGHN.DAL.Common;
using DHQGHN.DAL.Models.DHQG;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DHQGHN.DAL.DAO.DHQG
{
    public class KeHoachDAO : SqlDAO
    {
        public KeHoachDAO(string connectionString, System.Type type = null) :
            base(connectionString, type) { ;}


        public const string PROCEDURE_INSERT = "spKeHoach_Insert_TABLE";
        public const string PROCEDURE_GET_BY_NAMKEHOAH = "spKeHoach_GetByNamKeHoach";
        public const string PROCEDURE_GET_BY_NAMKEHOAH_NhomDV_NhomCT = "spKeHoach_GetByNamKeHoach_NhomDV_NhomCT";
        public const string PROCEDURE_DELETE_BY_NAMKEHOAH = "spKehoach_DeleteByNKH";
        public const string PROCEDURE_CHECK_IS_UPDATE_DIEU_CHINH = "spKehoach_CheckUpdateDieuchinh";
        public const string PROCEDURE_CHOT_DIEU_CHINH_GIUA_NAM = "spKehoach_Chot_DieuchinhGiuanam";
        public const string PROCEDURE_CHECK_PHAN_HOI = "spKehoach_CheckPhaHoiChot";
        public const string PROCEDURE_HUY_CHOT = "spKehoach_HuyChot";
        public const string PROCEDURE_DELETE_PHAN_HOI = "spKehoach_DeletePhanHoi";
        public const string PROCEDURE_SUM_CAN_DOI = "spKehoach_SUM_CANDOI";
        public const string PROCEDURE_GET_SUM = "spKehoach_GetByNamKeHoach_SUM";
        public const string PROCEDURE_GET_BY_OPTION = "spKehoach_GetByOption";
        public const string PROCEDURE_Update_By_Option = "spKehoach_Update_By_Option";
        public const string PROCEDURE_Add_By_Option = "spKehoach_Add_Chi_Tieu";
        

        public const string PROCEDURE_GET_DCCT_MANG = "spGetDieuChinhCTTheoMang";
        public const string PROCEDURE_GET_DCCT_DON_VI = "spGetDieuChinhCTTheoDonVi";
        
        public const string PROCEDURE_CAP1_GET_SUM = "spKehoach_GetByNamKeHoach_CAP1_SUM";
        public const string PROCEDURE_Rieng_GetByNamKeHoach = "spKeHoach_Rieng_GetByNamKeHoach";
        public const string PROCEDURE_GETBAN = "spKeHoach_KiemTraChiTieu";
        public const string PROCEDURE_DELETE_BY_DV_CT = "spKehoach_DeleteByDONVICHITIEU";
        public const string PROCEDURE_GET_BY_DV = "spKehoach_GetByDV";
        public const string PROCEDURE_GET_DIEU_CHINH_DHQG = "spGetDieuChinhDHQG";
        public const string PROCEDURE_GET_DIEU_CHINH_DHQG_CHUA_CT = "spGetDieuChinhDHQG_ChuaChinhThuc";
        public const string PROCEDURE_GET_DIEU_CHINH_DONVI = "spGetDieuChinhDonVi";
        public const string PROCEDURE_GET_DIEU_CHINH_DONVI_CHUA_CT = "spGetDieuChinhDonVi_ChuaChinhThuc";
        public const string PROCEDURE_GET_DIEU_CHINH_DONVI_CHUA_CT_ALL = "spGetDieuChinhDonVi_ChuaChinhThucAll";
        public const string PROCEDURE_GET_TONG_HOP_DIEU_CHINH_DONVI = "spGetTongHopDieuChinhDonVi";
        public const string PROCEDURE_GET_DIEU_CHINH_THEO_MANG = "spGetDieuChinhTheoMang";
        public const string PROCEDURE_GET_LST_DV_DIEU_CHINH = "spGetListDonViDieuchinhGiuaNam";

        public const string PROCEDURE_GET_DIEU_CHINH_DC03 = "spGetDieuChinhDonVi_DC03";
        public const string PROCEDURE_GET_KH_DHQG_BY_CHITIEU = "GetKeHoachDHQGByChiTieu";

        public const string PARAM_TABLE = "@Table";
        public const string PARAM_STATUS_ID = "@STATUS_ID";
        public const string PARAM_NHOMCHITIEU_ID = "@NHOMCHITIEU";
        public const string PARAM_NHOM_DON_VI_ID = "@NHOM_DON_VI_ID";
        public const string PARAM_BAN_ID = "@BAN_ID";
        public const string PARAM_FLAG = "@FLAG";
        public const string PARAM_NAM_KE_HOACH_ID = "@NAM_KE_HOACH_ID";
        public const string PARAM_DON_VI_ID = "@DON_VI_ID";
        public const string PARAM_CHI_TIEU_ID = "@CHI_TIEU_ID";
        public const string PARAM_GIA_TRI = "@GIA_TRI";


        public bool Save(List<KeHoach> data)
        {
            try
            {
                DataTable dataTable = GetDataTable(data);
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_INSERT);
                cmd.Parameters.Add(new SqlParameter(PARAM_TABLE, dataTable));
                int result = ExecuteNonQuery(cmd);

                return result > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }
        public List<GIATRI> GetkehoachbyOption(int namkh, int chitieu, int donvi, int status)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_BY_OPTION);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, donvi));
                cmd.Parameters.Add(new SqlParameter(PARAM_CHI_TIEU_ID, chitieu));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, status));
                return GetList<GIATRI>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public int UpdatekehoachbyOption(int namkh, int chitieu, int donvi, int status, string giatri)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_Update_By_Option);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, donvi));
                cmd.Parameters.Add(new SqlParameter(PARAM_CHI_TIEU_ID, chitieu));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, status));
                cmd.Parameters.Add(new SqlParameter(PARAM_GIA_TRI, giatri));
                return ExecuteNonQuery(cmd);
            }
            catch
            {
                throw;
            }
        }
        public int AddChitieubyOption(int namkh, int chitieu, int donvi, string giatri,int ban)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_Add_By_Option);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, donvi));
                cmd.Parameters.Add(new SqlParameter(PARAM_CHI_TIEU_ID, chitieu));
                cmd.Parameters.Add(new SqlParameter(PARAM_GIA_TRI, giatri));
                cmd.Parameters.Add(new SqlParameter(PARAM_BAN_ID, ban));
                return ExecuteNonQuery(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<GetBanChuaChot> Getbanchuachot(int namkh, int dv, int status)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GETBAN);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, dv));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, status));
                return GetList<GetBanChuaChot>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public int ChotDieuChinhGiuanam(int namkh)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_CHOT_DIEU_CHINH_GIUA_NAM);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                return ExecuteNonQuery(cmd);
            }
            catch
            {
                throw;
            }
            
        }
        public List<KeHoach> GetKehoachByNamKeHoach(int namkhid,int statusid,int flag)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_BY_NAMKEHOAH);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, statusid));
                cmd.Parameters.Add(new SqlParameter(PARAM_FLAG, flag));
                return GetList<KeHoach>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<KeHoach> GetKehoachByNamKeHoach_NhomDV_NhomCT(int namkhid, int statusid, int flag, int nhomdonvi, int nhomchitieu)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_BY_NAMKEHOAH_NhomDV_NhomCT);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, statusid));
                cmd.Parameters.Add(new SqlParameter(PARAM_NHOM_DON_VI_ID, nhomdonvi));
                cmd.Parameters.Add(new SqlParameter(PARAM_NHOMCHITIEU_ID, nhomchitieu));
                cmd.Parameters.Add(new SqlParameter(PARAM_FLAG, flag));
                return GetList<KeHoach>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<SUMCAP1> GetKehoach_CAP_1_ByNamKeHoach(int namkhid, int statusid)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_CAP1_GET_SUM);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, statusid));
                return GetList<SUMCAP1>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<SUMCANDOI> SumCanDOi(int namkhid, int statusid, int donvi)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_SUM_CAN_DOI);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, statusid));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, donvi));
                return GetList<SUMCANDOI>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<KeHoach> GetbyDV(int namkh, int status, int dv)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_BY_DV);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, status));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, dv));
                return GetList<KeHoach>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<KeHoach> GetKhRiengbyDV(int namkh, int donviid, int status)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_Rieng_GetByNamKeHoach);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, status));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, donviid));
                return GetList<KeHoach>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<SumKH> GetSumKH(int namkhid, int statusid)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_SUM);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, statusid));
                return GetList<SumKH>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public int DeleteKehoachbyNKH(int namkhid,int status,int nhomchitieu,int nhomdonvi)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_DELETE_BY_NAMKEHOAH);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, status));
                cmd.Parameters.Add(new SqlParameter(PARAM_NHOMCHITIEU_ID, nhomchitieu));
                cmd.Parameters.Add(new SqlParameter(PARAM_NHOM_DON_VI_ID, nhomdonvi));
                
                return ExecuteNonQuery(cmd);
            }
            catch
            {
                throw;
            }
        }
        public int DeleteKehoachbyDVCT(int namkhid, int chitieu, int dv)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_DELETE_BY_DV_CT);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, dv));
                cmd.Parameters.Add(new SqlParameter(PARAM_CHI_TIEU_ID, chitieu));
                return ExecuteNonQuery(cmd);
            }
            catch
            {
                throw;
            }
        }
        public int CheckUpdateDieuchinh(int namkhid, int status, int nhomchitieu, int nhomdonvi)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_CHECK_IS_UPDATE_DIEU_CHINH);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, status));
                cmd.Parameters.Add(new SqlParameter(PARAM_NHOMCHITIEU_ID, nhomchitieu));
                cmd.Parameters.Add(new SqlParameter(PARAM_NHOM_DON_VI_ID, nhomdonvi));

                return ExecuteNonQuery(cmd);
            }
            catch
            {
                throw;
            }
        }
        public int DeletePhanhoi(int namkhid, int status, int donviid, int nhomchitieu)
        {
            try
            {

                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_DELETE_PHAN_HOI);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, status));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, donviid));
                cmd.Parameters.Add(new SqlParameter(PARAM_NHOMCHITIEU_ID, nhomchitieu));
                return ExecuteNonQuery(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<ListChiTieu> CheckPhanHoi(int namkh, int status, int donviid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_CHECK_PHAN_HOI);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, status));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID,donviid));
                return GetList<ListChiTieu>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public int Huychot(int namkh, int status, int donviid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_HUY_CHOT);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, status));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donviid));
                return Convert.ToInt32(ExecuteScalar(cmd));
            }
            catch
            {
                throw;
            }
        }
        private DataTable GetDataTable(List<KeHoach> data)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("NAM_KE_HOACH_ID");
            dt.Columns.Add("DON_VI_ID");
            dt.Columns.Add("CHI_TIEU_ID");
            dt.Columns.Add("GIA_TRI");
            dt.Columns.Add("STATUS_ID");
            dt.Columns.Add("IS_CURRENT");
            dt.Columns.Add("NGUOI_DUNG_ID");
            dt.Columns.Add("STATUS_DATE");
            foreach (var item in data)
            {
                dt.Rows.Add(item.ID, item.NAM_KE_HOACH_ID, item.DON_VI_ID, item.CHI_TIEU_ID,item.GIA_TRI,item.STATUS_ID,item.IS_CURRENT,item.NGUOI_DUNG_ID,item.STATUS_DATE);
            }
            return dt;
        }
        public List<DIEU_CHINH_GIUA_NAM> GetDieuChinhDHQG(int namkhid, int statusid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_DIEU_CHINH_DHQG);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, statusid));
                return GetList<DIEU_CHINH_GIUA_NAM>(cmd);
            }
            catch
            {
                throw;
            }
        }

        public List<DIEU_CHINH_GIUA_NAM> GetDieuChinhDHQG_ChuaCT(int namkhid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_DIEU_CHINH_DHQG_CHUA_CT);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                return GetList<DIEU_CHINH_GIUA_NAM>(cmd);
            }
            catch
            {
                throw;
            }
        }

        

        public List<DIEU_CHINH_GIUA_NAM> GetDieuChinhDonVi(int namkhid, int donviid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_DIEU_CHINH_DONVI);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, donviid));
                return GetList<DIEU_CHINH_GIUA_NAM>(cmd);
            }
            catch
            {
                throw;
            }
        }

        public List<BC_DIEU_CHINH_GIUA_NAM> GetDieuChinhDonViChuaChinhThuc(int namkhid, int donviid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_DIEU_CHINH_DONVI_CHUA_CT);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, donviid));
                return GetList<BC_DIEU_CHINH_GIUA_NAM>(cmd);
            }
            catch
            {
                throw;
            }
        }

        public List<BC_DIEU_CHINH_GIUA_NAM> GetDieuChinhDonViChuaChinhThucAll(int namkhid, int donviid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_DIEU_CHINH_DONVI_CHUA_CT_ALL);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, donviid));
                return GetList<BC_DIEU_CHINH_GIUA_NAM>(cmd);
            }
            catch
            {
                throw;
            }
        }

        public List<DIEU_CHINH_GIUA_NAM> GetTongHopDieuChinhDonVi(int namkhid, int donviid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_TONG_HOP_DIEU_CHINH_DONVI);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, donviid));
                return GetList<DIEU_CHINH_GIUA_NAM>(cmd);
            }
            catch
            {
                throw;
            }
        }

        public List<SumKH> GetDieuChinhTheoMang(int namkhid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_DCCT_MANG);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                return GetList<SumKH>(cmd);
            }
            catch
            {
                throw;
            }
        }

        public List<SumKH> GetDieuChinhTheoDonVi(int namkhid, int donviid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_DCCT_DON_VI);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI_ID, donviid));
                return GetList<SumKH>(cmd);
            }
            catch
            {
                throw;
            }
        }

        public List<DV_DIEU_CHINH> GetListDonViDieuChinh(int namkhid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_LST_DV_DIEU_CHINH);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                return GetList<DV_DIEU_CHINH>(cmd);
            }
            catch
            {
                throw;
            }
        }


        public List<BC_DIEU_CHINH_GIUA_NAM> GetDieuChinhDC03(int namkhid, int maChiTieuCap1)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_DIEU_CHINH_DC03);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter("@MA_CAP1", maChiTieuCap1));
                return GetList<BC_DIEU_CHINH_GIUA_NAM>(cmd);
            }
            catch
            {
                throw;
            }
        }


        public double GetKhDHQGByChiTieu(int namkhid, int maChiTieu)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_KH_DHQG_BY_CHITIEU);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter("@CHI_TIEU_ID", maChiTieu));

                List<GIATRI1> ret = GetList<GIATRI1>(cmd);
                if (ret.Count > 0) return ret[0].GIA_TRI;
                else return 0;
            }
            catch
            {
                throw;
            }
        }
    }
}
